package com.jx.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.catalina.valves.JDBCAccessLogValve;

import com.jx.dao.CatDao;
import com.jx.model.Category;
import com.jx.util.JDBCUtil;

public class CatDaoImpl implements CatDao {
	
// 显示商品分类
	@Override
	public List<Category> showCatrgory(int start,int pageSize) {
		// 定义商品类的对象数组 cs
		List<Category> cs = new ArrayList<Category>();
		// 获取数据库连接
		Connection con = JDBCUtil.getConnection();
		// 定义StringBuffer类型的sql语句
		StringBuffer sql = new StringBuffer("SELECT * FROM j_category LIMIT ?,?");
		// 预编译执行sql语句 注意：执行StringBuffer类型的sql语句是要调用toString()方法
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setInt(1, start);
			pst.setInt(2, pageSize);
			// 定义接收查询结果的结果集
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				// 创建商品类对象
				Category c = new Category();
				// 通过查询出来的结果实例化商品分类对象
				c.setCid(rs.getInt("cid"));
				c.setCname(rs.getString("cname"));
				// 将对象加入到集合里
				cs.add(c);
			}
			// 关闭连接与编译
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cs;
	}
	
//查出所有商品分类的数量
	@Override
	public int showCategoryCount() {
		// TODO Auto-generated method stub
		int ccount = 0;
		//获取数据库连接
		Connection con = JDBCUtil.getConnection();
		//定义StringBuffer类型的sql语句
		StringBuffer sql = new StringBuffer("SELECT COUNT(cid) ccount FROM j_category");
		//预编译
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				ccount = rs.getInt("ccount");
			}
			//关闭编译与连接
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ccount;
	}
//增加商品分类
	@Override
	public boolean addCategory(Category c) {
		// TODO Auto-generated method stub
		//定义布尔型变量flag初始值为false
		boolean flag = false;
		//获取数据库连接
		Connection con = JDBCUtil.getConnection();
		//定义StringBuffer类型的sql语句
		StringBuffer sql = new StringBuffer("INSERT INTO j_category (cname) VALUES (?);");
		//预编译
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, c.getCname());
			int i = pst.executeUpdate();
			if (i>0) {
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

//删除商品分类
	@Override
	public boolean delCategory(int cid) {
		// TODO Auto-generated method stub
		// 定义布尔型变量flag
		boolean flag = false;
		// 获取数据库连接
		Connection con = JDBCUtil.getConnection();
		// 定义StringBuffer类型的sql语句
		StringBuffer sql = new StringBuffer("DELETE FROM j_product WHERE cid=?");
		StringBuffer sql1 = new StringBuffer("DELETE FROM j_category WHERE cid=?;");
		// 预编译 执行sql语句 注意toString()方法
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			PreparedStatement pst1 = con.prepareStatement(sql1.toString());
			pst.setInt(1, cid);
			pst1.setInt(1, cid);
			pst.executeUpdate();
			int i = pst1.executeUpdate();
			if (i > 0) {
				flag = true;
			}
			// 关闭连接和编译
			JDBCUtil.closeJDBC(pst, con);
			JDBCUtil.closeJDBC(pst1, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
//模糊查询分页
	@Override
	public List<Category> showCategory(String cname, int start, int pageSize) {
		// 定义商品类的对象数组 cs
				List<Category> cs = new ArrayList<Category>();
				// 获取数据库连接
				Connection con = JDBCUtil.getConnection();
				// 定义StringBuffer类型的sql语句
				StringBuffer sql = new StringBuffer("SELECT * FROM j_category WHERE cname LIKE ? LIMIT ?,?");
				// 预编译执行sql语句 注意：执行StringBuffer类型的sql语句是要调用toString()方法
				try {
					PreparedStatement pst = con.prepareStatement(sql.toString());
					pst.setString(1,"%"+cname+"%");
					pst.setInt(2, start);
					pst.setInt(3, pageSize);
					// 定义接收查询结果的结果集
					ResultSet rs = pst.executeQuery();
					while (rs.next()) {
						// 创建商品类对象
						Category c = new Category();
						// 通过查询出来的结果实例化商品分类对象
						c.setCid(rs.getInt("cid"));
						c.setCname(rs.getString("cname"));
						// 将对象加入到集合里
						cs.add(c);
					}
					// 关闭连接与编译
					JDBCUtil.closeJDBC(pst, con);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return cs;
	}
//更新商品分类
	@Override
	public boolean updCategory(Category c) {
		// TODO Auto-generated method stub
		//定义布尔型变量flag默认初始值为false
		boolean flag = false;
		//获取数据库连接
		Connection con = JDBCUtil.getConnection();
		//定义StringBuffer类型的sql语句
		StringBuffer sql = new StringBuffer("UPDATE j_category SET cname=? WHERE cid = ?;" );
		//预编译，执行sql语句，使用StringBuffer定义的sql语句需要调用toString()方法
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1,c.getCname());
			pst.setInt(2, c.getCid());
			int i = pst.executeUpdate();
			if (i>0) {
				flag=true;
			}
			//关闭数据库连接与编译
			JDBCUtil.closeJDBC(pst, con);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
//通过商品类名查找商品类
	@Override
	public boolean findCatByCname(String cname) {
		// TODO Auto-generated method stub
		boolean flag = false;
		Connection con = JDBCUtil.getConnection();
		StringBuffer sql = new StringBuffer("SELECT * FROM j_category WHERE cname = ?");
		try {
			PreparedStatement pst = con.prepareStatement(sql.toString());
			pst.setString(1, cname);
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

}
